Completing a Physical Inventory

Process Definition:

Physical Inventory is a way to update Deacom with actual physical counts of inventory in Facilities. Physical Inventory Worksheets create the master list of items to be counted. The worksheet is a snapshot of inventory as of a selected date (typically the current date). Worksheets can be setup to include Locations, Lot numbers, and serial numbers in the count. 

Configuration:

  • Inventory > Maintenance > Facilities/Location Types/Locations:
    • At least 1 of each should be setup.
  • Inventory > Item Master > Modify > Units tab:
    • Count Unit - The count unit provides the default unit of measure when creating physical inventory worksheets.

Process in Deacom:

Preventing Inventory Transactions prior to Counting

The ‘Prevent Inventory Transaction’ flag should be checked prior to creating the Cycle Count Worksheet. It is available at the Facility, Zone, Location Type, Location, and Item Master Level. Deacom recommends checking the flag on the Facility level. 

Note: While the ‘Prevent Inventory Transaction’ flag is not required, companies should understand that not having this flag checked does not mean business as usual when performing inventory transactions. Users will need to record any moves, adjustments and issuing of items and lots, either on paper or via spreadsheets/word documents. In addition, inventory transactions should be minimized. Having the ‘Prevent Inventory Transaction’ flag unchecked significantly increases the risks of inaccurate physical inventories. Deacom highly recommends locking down inventory transactions.

  1. Navigate to Inventory > Maintenance > Facilities, Location Types, Locations, etc.
  2. Click Modify on the record.
  3. Check ‘Prevent Inventory Transactions’.
  4. Click Save.

Creating the Physical Inventory Worksheet

  1. Navigate to Print Outs > Physical Inventory Forms.
  2. Ensure the ‘Report Type’ is “Physical Inventory”.
  3. Select the criteria from the pre-filter.
  4. Enter a ‘Worksheet Name’ with no spaces.
  5. Click Print or Export & Print.
    1. Print – prints the worksheet only; does not create a saved inventory worksheet for posting against.
    2. Export & Print – prints the worksheet AND creates the cycle count inventory worksheet for posting purposes.

Performing the Count and Inputting Counts 

Users have three ways to enter counts into Deacom:

  1. Manual Entry – Counts can be entered via manual entry, using the printed worksheet as a guide, into the Deacom main application.
  2. Imported via Excel – Counts can be imported via an Excel spreadsheet, using a specific configuration.
  3. Entered on WMS – Most common, counts can be entered using the WMS scanner. This method proves to be very user friendly, as operators can scan inventory barcodes and Location labels to help expedite the process.

Often times new lots are discovered during the physical inventory process. When a user adds a new lot to the worksheet, the lot cost will be set based on the costing method setup by the organization in Accounting Options. For companies setup to cost by standard cost or average cost, the cost is derived from the Standard or Average cost, respectively. For companies setup to cost by FIFO cost, the cost is set from the Last Cost. If the Last Cost is $0.00, the system sets the Lot cost to the Standard cost of the item. For multi-Facility organizations, each of these costs is derived from the Facility Part Cross Reference. When a Part on the count sheet cannot be matched to an item with an Item Master entry in the system, this prompt: “The following parts do not have existing Item Master records in Deacom and therefore will not be counted” is displayed and counting continues for valid Parts.

Using WMS

See the WMS - Completing Cycle Counts & Physical Inventories page for more information. 

Importing Counts from Microsoft Excel

Many organizations choose to complete counts via Microsoft Excel spreadsheets. How a company obtains the counts and adds them to the spreadsheet is their choice. Once the counts have been added to the spreadsheet and the spreadsheet has been properly formatted, the spreadsheet may be imported into Deacom as outlined below. The data that should be included in the spreadsheet varies based on the “Group By” option selected when creating the physical inventory worksheet via Print Outs > Physical Inventory Forms. 

Creating the Spreadsheet

  1. Ensure the spreadsheet is created via Microsoft Excel. The spreadsheet must be saved in XLSX format.
  2. The spreadsheet may contain up to thirteen columns, and each column must contain a heading as indicated in the list below. Only the first 3 columns are required (Note: although the User Lot is NOT required, Deacom will require an entry. So, if the Lot is New to Deacom, a User Lot number should be added).
    1. pr_codenum: Deacom part number.
    2. c2_quant: Quantity (All c2_quants should be greater than zero. Empty cells, zeros, and negative numbers can cause the import to fail).
    3. c2_loid: Location ID number (the c2_loc column is not supported in version 14.3+ because of the addition of set Locations/Location Types and the possibility of having the same Locations within different Location Types)
    4. c2_syslot: System lot number.
    5. c2_userlot: User lot number.
    6. c2_lotdate: User lot date.
    7. c2_attrib1: Lot attribute1.
    8. c2_attrib2: Lot attribute2.
    9. c2_attrib3: Lot attribute3.
    10. c2_loc: Location.
    11. c2_serial: Serial number.
    12. c2_catchwgt: Catch weight.
    13. c2_notes: Notes.
  3. Save and Close the spreadsheet.

Importing the Spreadsheet

  1. Navigate to Inventory > Physical Inventory.
  2. Fill in the pre-filter information to narrow down results.
  3. Click View.
  4. Select the worksheet and click Count Sheets.
  5. Click Add.
    1. Enter the ‘Sheet Name’.
    2. Click Import Excel.
      1. Search for the closed excel file.
      2. Click Import.
    3. The lines from the spreadsheet will load.
    4. At this point, imported information can be edited.
    5. Click Save to have the counts added to the physical inventory worksheet.

Note: The system checks when users import count sheets from a spreadsheet to ensure that a Location and Lot Number are specified when the physical inventory sheet is grouped by "Part/Location/Lot/Serial”. If the count sheet is missing either a Lot or Location, the system will prompt the user to add this information to the Excel sheet before it can be imported.

Manually Entering Counts

  1. Navigate to Inventory > Physical Inventory.
  2. Fill in the pre-filter information to narrow down results.
  3. Click View.
  4. Highlight the worksheet and click Modify.
  5. Double-click or highlight and click Modify on existing lines to enter the count.
    1. Enter the counted quantity in ‘Adjust To’.
    2. Confirm the remaining information.
    3. Click Next to continue or click Save and Exit once completed.
  6. To add a new line (User Lot/System Lot not in Deacom), click +.
    1. Enter the ‘Part Number’.
    2. Enter the counted quantity in ‘Adjust To’.
    3. Enter the User ‘Lot’ and ‘System Lot’.
    4. Enter the ‘New Location Type’ and ‘New Location’.
    5. Click Next to continue or click Save and Exit once completed.
  7. Click Save once finished adding counts.

Posting Physical Inventory Counts

  1. Navigate to Inventory > Physical Inventory.
  2. Fill in the pre-filter information to narrow down results.
  3. Click View.
  4. Highlight the worksheet and click Modify.
  5. Set an ‘Adjustment Account’.
  6. The counts by part numbers by location and lot will show in the ‘Count’ column.
  7. Click the Set Adjust To button in the header to accept the counts which were input. This will set the ‘Adjust To’ column to the count.
    1. The ‘Unit Var.’ and ‘Dollar Var.’ columns will show the variance between the current on-hand quantity/cost and the quantity to which the part will be adjusted to.
  8. Click Save.
  9. Once all quantities have been confirmed and you are ready to finalize the counts, click the Post Changes button in the header to post the inventory counts.
  10. Click Yes when prompted ‘Are you sure you want to post changes?’.

Note: Version 17.01.110 adds the "Allow Inventory Transactions" checkbox to the Edit Worksheet form. When this field is checked, the system removes the lock on items/inventory included in the physical inventory worksheet at the completion of the physical inventory, thus allowing the items/inventory to be used for transactions throughout the system.